package com.demo.java.spring.tx;

import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import javax.persistence.LockModeType;
import java.util.Optional;

/**
 create table catalog  (
     id int(11) unsigned primary key AUTO_INCREMENT COMMENT '主键',
     name varchar(50) NOT NULL DEFAULT '' COMMENT '商品名称',
     browse_count int(11) NOT NULL DEFAULT 0 COMMENT '浏览数',
     version int(11) NOT NULL DEFAULT 0 COMMENT '乐观锁，版本号'
 ) ENGINE=INNODB DEFAULT CHARSET=utf8;

 CREATE table browse (
     id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
     cata_id int(11) NOT NULL COMMENT '商品ID',
     user varchar(50) NOT NULL DEFAULT '' COMMENT '',
     create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
     PRIMARY KEY(id)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8;

 */
public interface CatalogRepository extends CrudRepository<Catalog, Long> {

    @Query(value = "select * from Catalog a where a.id = :id for update", nativeQuery = true)
    Optional<Catalog> findCatalogsForUpdate(@Param("id") Long id);

    @Lock(value = LockModeType.PESSIMISTIC_WRITE) //代表行级锁
    @Query("select a from Catalog a where a.id = :id")
    Optional<Catalog> findCatalogWithPessimisticLock(@Param("id") Long id);

    @Modifying(clearAutomatically = true) //修改时需要带上
    @Query(value = "update Catalog set browse_count = :browseCount, version = version + 1 where id = :id " +
            "and version = :version", nativeQuery = true)
    int updateCatalogWithVersion(@Param("id") Long id, @Param("browseCount") Long browseCount, @Param("version") Long version);


}
